Displaying metrics from an alternative representation of a database

ABSTRACT

A system and method comprises selecting at least one metric from an alternative representation of a database to create a request based on the at least one metric, invoking an interpreter associated with the database to return data related to the at least one metric, and displaying the data.

BACKGROUND

Some computer systems may be adapted to analyze data and generatereports based on the analyzed data. For example, a system may reportprofits by analyzing revenue and expenditures. A reporting applicationmay be responsible for analyzing relevant data and generating thereports.

In many situations, the reports generated by such a system utilize datacollected at multiple levels of the organization. Mappings (such asviews), data structures, and complex queries may need to be generatedand executed by the reporting application to relate and transform thedata into user friendly information. Unfortunately, the performance ofsuch systems may be adversely affected by the generation and executionof such mappings.

BRIEF SUMMARY

The problems noted above are solved in large part by a method and systemfor displaying metrics from an alternative representation of a database.One of the exemplary embodiments is a method that comprises selecting atleast one metric from an alternative representation of a database tocreate a request based on the at least one metric, invoking aninterpreter associated with the database to return data related to theat least one metric, and displaying the data.

BRIEF DESCRIPTION OF THE DRAWINGS

For a detailed description of exemplary embodiments of the invention,reference will now be made to the accompanying drawings in which:

FIG. 1 shows a system constructed in accordance with embodiments of theinvention and including a speech recognition module;

FIG. 2 shows a relationship between the reporting application and thedatabase of FIG. 1;

FIG. 3 illustrates an exemplary business data model in accordance withembodiments of the invention;

FIG. 4 illustrates an exemplary deployment procedure in accordance withembodiments of the invention; and

FIG. 5 illustrates an exemplary report generation procedure inaccordance with embodiments of the invention.

NOTATION AND NOMENCLATURE

Certain terms are used throughout the following description and claimsto refer to particular system components. As one skilled in the art willappreciate, various companies may refer to a component by differentnames. This document does not intend to distinguish between componentsthat differ in name but not function. In the following discussion and inthe claims, the terms “including” and “comprising” are used in anopen-ended fashion, and thus should be interpreted to mean “including,but not limited to . . . .” Also, the term “couple” or “couples” isintended to mean either an indirect or direct electrical connection.Thus, if a first device couples to a second device, that connection maybe through a direct electrical connection, or through an indirectelectrical connection via other devices and connections. As used herein,the term “database” may comprise a set of data and an interpreter. Theinterpreter may be capable of processing queries to retrieve and storedata to the set of data.

DETAILED DESCRIPTION

The following discussion is directed to various embodiments of theinvention. Although one or more of these embodiments may be preferred,the embodiments disclosed should not be interpreted, or otherwise used,as limiting the scope of the disclosure. In addition, one skilled in theart will understand that the following description has broadapplication, and the discussion of any embodiment is meant only to beexemplary of that embodiment, and not intended to intimate that thescope of the disclosure, is limited to that embodiment.

FIG. 1 shows a reporting system 100 configured in accordance withembodiments of the invention. As shown, system 100 comprises a computersystem 102, a database 104, and a display 106. The database 104 maystore various types of business data, such as supplier and customerinformation, pending orders, service level agreements (SLAs), andinventory. In some embodiments of the invention, the business data maybe distributed in a plurality of databases.

The computer system 102 may be any type of computer system, such as alaptop computer, a personal computer, and/or a stand-alone computeroperated as a server. The computer system 102 comprises a centralprocessing unit (CPU) 108, a memory 110, and an input/output (I/O)interface 112. The memory 110 may comprise any type of volatile ornon-volatile memory, such as random access memory (RAM), read-onlymemory (ROM), and a hard drive. Stored within the memory 110 are one ormore reporting applications 114. The reporting application 114 may be acomputer program designed to analyze the business data stored indatabase 104 and generate reports that are presented on the display 106.

A schema may organize existing business data in the database 104 anddefine relationships between the business data. For example, the schemamay define tables, columns, and constraints for the business data storedin the database 104. In some embodiments, the database 104 may be a flatfile or spreadsheet that stores the business data.

The I/O interface 112 couples together the database 104 with thecomputer system 102 and facilitates the exchange of data between thedatabase 104 and the computer system 102. Although not specificallyshown, the database 104 may be coupled to a network, such as theInternet, and the I/O interface may comprise a network adapter thatfacilitates the exchange of data between the database 104 and thenetwork adapter.

The embodiments of the invention provide a framework for efficientlydefining and computing business metrics from the database 104. As usedherein, a business metric is a measurable property of elementsassociated with a business entity. The business entity may comprise anyexisting object defined by the schema of the database 104. For example,orders and customers, as well as related information, such as the orderquantity and the products of an order, may be business entities. Ametric associated with the business entities may represent thefulfillment or cost of a customer's orders. The metric may bequantitative, resulting in a numerical value, or qualitative, such as ataxonomy that places each element of an entity into groups.

FIG. 2 shows the relationship between the database 104 and the reportingapplication 114. As shown, one or more metrics 208-214 utilize datagenerated by one or more mappings 202-206. The mappings 202-206 maycomprise computer readable code, such as structured query language (SQL)and Java® classes, that interact with the underlying schema associatedwith the database 104 to compute the one or more metrics 208-214. Thecomputer readable code may be executed by the interpreter of thedatabase. The metrics 208-214 may provide meaning, or semantics, for thedata mapped by the mappings 202-206. For example, a mapping may retrievecustomers that have placed over ten orders in the last week from thedatabase 104. A metric may utilize the results of this exemplary mappingto place each customer into a two-level taxonomy. The taxonomy mayclassify each customer as a high priority customer or a low prioritycustomer based upon the results of the mapping. The reportingapplication 114 may utilize one or more metrics 208-214 that utilize theexemplary mapping to generate reports that are displayed on the display106.

Referring now to FIG. 3, a business data model 300 is shown inaccordance with at least some embodiments of the invention. As shown,the business data model 300 comprises an entities table 302, anattributes table 304, a mappings table 306, a metrics table 308, and areports table 310. The cardinality of the relationships within thebusiness data model 300 are shown. In accordance with embodiments of theinvention, the business data model 300 acts as an abstraction layerbetween the database 104 and the reporting application 114. Theabstraction layer provides an alternative representation of the database104 to the reporting application 114. The business data model 300 mayhold data concerning the existing schema of the database 114, as well asdata concerning the metrics and reports of interest to a user of thereporting application 114. In addition, the business data model 300 mayhold the mappings 202-206 used to compute the metrics 208-214 from theexisting business data.

The business data model 300 may be incorporated into the existing schemaof the database 104 or defined as a new schema in the database 114.Thus, after deployment of embodiments of the invention, the schema ofthe existing business data and a schema for the new business data model300 may exist in the database 114. The business data model 300 includesa description of the existing business data and also describes how tomap the existing business data into the metrics 208-214. The metrics208-214 may be utilized to generate and display reports to a user of thereporting application 114.

Upon deployment of embodiments of the invention, a description of eachexisting business entity in the database 104 may be stored in theentities table 302. Table 1 illustrates an exemplary entities table 302with exemplary data. As shown, the table comprises four columns: name,base_table, ID_column, and element_column. As with each table in theexemplary business model 300, other columns required by the database104, such as a system assigned key, may also be used.

TABLE 1 ENTITIES ENTITY_(—) ELEMENT_(—) NAME BASE_TABLE ID_COLUMN COLUMNOrders Orders_Received Order_ID Descriptive_Name . . . . . . . . . . . .

The column entity_name may store a descriptive name for the entity, suchas “Orders.” The descriptive name may be utilized by the reportingapplication 114 when generating and displaying reports. The columnbase_table may store the name of the table in the existing schema of thedatabase 104 that holds the data associated with the entity, such as“Orders_Received.” The column ID_column may store the name of the columnin the base_table that holds the key of the base_table, such as“Order_ID.” The column element_column may store the column in thebase_table that holds the name of the elements associated with theentity.

By accessing the entities table 302, the reporting application 114 mayretrieve information associated with the business entities. In addition,the reporting application 114 may retrieve information regarding whichtables in the existing schema store information about the businessentities. Since the structure of the entities table 302 may be definedregardless of the entities being described, the reporting application114 may be generic and independent from the specific business domainused to employ the embodiments of the invention.

Entities stored in the entities table 302 may have relationships withone or more other entities. Aggregation logic may define howaggregations may be performed on the relationships. For example, anorders entity may be related to a products entity. Aggregation logic maydefine how to group orders based on the product ordered. The aggregationlogic may be implemented via any type of computer readable codesupported by the database 104, such as SQL or Java® classes. Theaggregation logic may be dependent only on the entities, and not on themetrics and reports defined over such entities. Hence, the aggregationlogic used to aggregate orders based on the product is independent ofthe specific metrics defined over the orders entity. Once theaggregation logic for aggregating orders based on the product has beendefined, it may also be possible to group any order-related metric(e.g., the order processing cost) by product entity (e.g., to retrievethe average processing cost by product). Thus, the aggregation logic isseparate from any reporting logic utilized by the reporting application114. This separation of logic enables the reuse of the aggregation logicfor the generation of a multiplicity of reports defined on differentmetrics.

Table 2 illustrates an exemplary attributes table 304 with exemplarydata. As shown, the table comprises four columns: entity_name,attribute_name, data_type, and attribute_column. Greater or fewercolumns may be used depending upon the specific implementation chosen.

TABLE 2 ATTRIBUTES ENTITY_(—) ATTRIBUTE_(—) NAME ATTRIBUTE_NAMEDATA_TYPE COLUMN Orders Time_Placed Date Order_Date . . . . . . . . . .. .

The column entity_name may store the related entity_name from theentities table 302. The column attribute_name may store a descriptivename of the attribute that may be utilized by the reporting application114 when generating and displaying reports. The data_type may store thetype of data associated with the attribute, such as a string, date, orinteger. The attribute_column may store the name of the column in thebase table of the existing business data associated with the entity thatholds the attribute. The exemplary data in Table 2 may represent anattribute Time_Placed that represents the date an order was placed. Thecombination of the entitles table 302 and attributes table 304 may beutilized with a variety of business domains to describe the associatedbusiness data.

Table 3 illustrates an exemplary mappings table 306 and exemplary data.As shown, the table comprises four columns: mapping_name, data_type,mapped_entity, and mapping_function. Greater or fewer columns may beused depending upon the specific implementation chosen. The mappingstable 306 may also comprise one or more columns that store the name andtype of any mapping_function parameters.

TABLE 3 MAPPINGS MAPPING_(—) DATA_(—) MAPPING_(—) NAME TYPEMAPPED_ENTITY FUNCTION Total_Cost Integer Customer_(—) Select . . . From. . . . . . . . . . . . . . .The column mapping_name stores a descriptive name of the mapping thatmay be displayed to user of the system 100 by the reporting application114, such as “Total Cost.” The column data type stores the type of datareturned by the mapping_function, such as “Integer.” The columnmapped_entity stores the name of the entity to which the mappingfunction is applied. In the example above, the mapping computes thetotal cost for each customer. The column mapping_fuction stores thefunction that computes values from the existing business data in thedatabase 104. The semantics to these values may be defined by themetrics, as discussed below. The mapping function may be implemented asany type of compute readable code, such as SQL queries and Java®classes.

Table 4 illustrates an exemplary metrics table 308. As shown, the tablecomprises three columns: metric_name, data_type, and metric_entity.Greater or fewer columns may be used depending upon the specificimplementation chosen.

TABLE 4 METRICS METRIC_NAME DATA_TYPE METRIC_ENTITY Preferred_CustomerBoolean Customers . . . . . . . . .

The column metic_name stores a descriptive name of the metric beingcomputed, in this example “Preferred_Customer.” The column data_typestores the type of data the associated with the metric, such as“Boolean” (a special case of taxonomical metric in which the classes areTRUE or FALSE). The column metric_entity stores the name of the entitywhose elements are measured by the metric, such as “Customers.” Theexample metric in Table 4 may represent a taxonomy that portrays if acustomer is “preferred.” The preferred status may be assigned tocustomers who spend a threshold amount or placed a threshold number oforders.

Some embodiments of the invention allow the association of metrics withmappings. This association denotes that a particular mapping functionmay be used to compute a particular metric. A separate table, calledmeters, may be defined in the business data model 300 to store thisassociation. Table 5 illustrates an exemplary meters table withassociated data.

TABLE 5 METERS METRIC MAPPING CONTEXT Preferred_Customer cost_less_thanCustomer_XYZ . . . . . . . . .

The exemplary data is associated with the metric preferred_customer. Themetric may be computed by running function cost_less_than (that defineswhether the cost for processing customers order is less than a certainamount). However, the function may only be executed for a specific acustomer name customer_XYZ. The amount associated with the metric may bea parameter of the mapping function. Any parameters of the mappingfunction may be stored in a separate column (not specifically shown) ofthe meters table.

The reports 310 table may store reports generated by the reportingapplication 114. The reporting application 114 utilizes, at least inpart, the metrics defined in the metrics table 308 to generate thereports displayed on the display 106.

In at least some embodiments, the mappings 202-206 are defined by SQLqueries. The following SQL query is a (Boolean) mapping function thatruns over the existing business data in the database 104 and returns<quote request identifiers, value> pairs, where the value corresponds tothe time taken by the hub to respond to the request for quote.

-   -   SELECT QUOTE_UUID, DURATION    -   FROM QUOTE_REQUESTS Q

The next exemplary mapping function is a Boolean mapping that returnsall orders processed in a time that exceeded a time specified as part ofthe service level agreement stipulated with a customer. The valueassociated with this exemplary mapping may be understood to be TRUE.

-   -   SELECT QUOTE_UUID    -   FROM QUOTE_REQUESTS Q, SLA, CUSTOMER C    -   WHERE Q.CUSTOMER_ID=C.ID AND    -   LA.CUSTOMER_ID=C.ID AND    -   Q.DURATION>SLA.MAX_DURATION

In some embodiments of the invention, mapping functions may return morethan <element ID, value> pairs. For example, elements being analyzed mayfrequently be associated with a start and an end time. A timestamp maybe generated by the database 104 to obtain the start and end time value.

A more complex example of a mapping function utilizing parameters and atimestamp is shown below. The exemplary mapping function associates eachquote request with the value of one of its attributes, whose name is aparameter of the mapping. The mapping parameter is enclosed betweenpercentage symbols. The mapping parameter may be replaced with code thatretrieves the actual parameters, as previously discussed, whenassociated with one or more meters.

-   -   SELECT Q.QUOTE_UUID,QD.VALUE, Q.TIMESTAMP    -   FROM QUOTE_REQUESTS Q,    -   QUOTE_DATA QD, QUOTE_DATA_DEFS QDD    -   WHERE QDD.NAME=%NAME%    -   AND QDD.ID=QD.DATA_DEF_ID AND    -   QD.QUOTE_UUID=Q.QUOTE_UUID

One or more metrics may refer to the same mapping, possibly applied todifferent contexts (e.g., the notion of preferred customer may becomputed using different functions based on the customer type) and usingdifferent parameters. This enables mapping function code to be reused byseveral metrics.

Referring now to FIG. 4, an exemplary deployment procedure is show inaccordance with embodiments of the invention. As shown, the procedurebegins (block 400) and steps to creating the business data model 300based on the existing business data in database 114 (block 402). Afterthe data model 300 has been created, entities (and attributes associatedwith the entities) may be defined and stored into the entities table 302and the attribute table 304 of the data model 300 (block 404).Aggregation logic associated with the defined entities may be definedand stored for relationships between the entities (block 406). Metricsand mapping then may be defined and stored in the metrics table 308 andthe mappings table 306, as well as in the meters table (block 408). Aspreviously discussed, each mapping may be associated with an SQLfunction or statement that maps data from the existing schema for usewith the metrics 208-214. The reporting application 114 may be utilizedto generate one or more reports (block 410), thereby ending theprocedure (block 412).

FIG. 5 shows an exemplary report generation procedure in accordance withembodiments of the invention. As shown, the procedure begins (block 500)and then proceeds to the execution of the reporting application 114(block 502). Reports may be selected via the reporting application 114(block 504) based on input from a user of the reporting application 114.The reporting application 114 may determine which metrics the report isbased (block 506). After determination of the metrics, the correspondingmappings may be determined by accessing the meters table (block 508).The determined mappings may then be executed by the database interpreter(block 510). The generation of a report may involve formatting the datareceived into a visual format, such as graphs and tables, and also mayinvolve associated processing by the reporting application 114 (block511). For example, the data received from block 508 may be rounded bythe reporting application based on a preference of the user. Aftergeneration, the report may be displayed on the display 106 (block 512),and the procedure may end (block 514). As can be appreciated, theforegoing procedure may be performed in fewer or more steps and may beperformed in a different order than the one provided.

The above discussion is meant to be illustrative of the principles andvarious embodiments of the present invention. Numerous variations andmodifications will become apparent to those skilled in the art once theabove disclosure is fully appreciated. It is intended that the followingclaims be interpreted to embrace all such variations and modifications.

1. A processor-based method comprising: selecting, by a user, at leastone metric from an alternative representation of a database of existingdata; invoking a mapping associated with the at least one metric tocreate a search query; invoking an interpreter to execute the searchquery and return data related to the search query; and displaying thedata related to the search query; generating a request based on theexisting data from the request based on the at least one metric prior tothe invoking; and wherein the invoking further comprises invoking theinterpreter using the request based on the at least one metric.
 2. Themethod as defined in claim 1 wherein selecting further comprisesselecting from the alternative representation of the database whereinthe alternative representation is a reduced version of the existingdata.
 3. The method as defined in claim 1 wherein selecting furthercomprises selecting a metric.
 4. The method as defined in claim 1wherein selecting further comprises selecting the at least one metricfrom the alternative representation of the database of existing data tocreate a generic structured query language (SQL) request based on the atleast one metric.
 5. The method as defined in claim 4 wherein generatingfurther comprises generating a specialized SQL request based on theexisting data from the request based on the at least one metric.
 6. Themethod as defined in claim 1 wherein the selecting further comprisesselecting at least one metric from the alternative representation of thedatabase of existing data, the alternative representation incorporatedwith the existing data in the database.
 7. A computer readable mediumstoring a program that, when executed by a processor of a computer,performs a method comprising: maintaining existing data; storing metricsrelated to the existing data; mapping the existing data to the metrics;and providing access to the existing data by referencing the metrics;wherein the reporting program allows a user to select at least onemetric from an alternative representation of a database to create arequest based on the at least one metric, and wherein an interpreter ofthe database modifies the request to pertain to the previously createddata.
 8. The computer readable medium as defined in claim 7 whereinmapping further comprises mapping the existing data to a set ofpredefined metrics.
 9. The computer readable medium as defined in claim8 wherein mapping further comprises utilizing a general mapping table torelated the existing data to the predetermined metrics.
 10. The computerreadable medium as defined in claim 7 wherein the providing furthercomprises generating a specific query to the existing data using themetrics.
 11. A system comprising: a computer system having a centralprocessing unit (CPU); a memory coupled to the CPU, the memory storing areporting application executable by the CPU; a database coupled to thecomputer system, the database storing a previously created data set;wherein the database comprises an alternative representation of thepreviously created data and an interpreter that executes search queriesgenerated from mappings stored in the alternative representation;wherein the reporting program allows a user to select at least onemetric from the alternative representation of the database to create arequest based on the at least one metric, and wherein an interpreter ofthe database modifies the request to pertain to the previously createddata.
 12. The system as defined in claim 11 wherein the database is partof the computer system.
 13. The system as defined in claim 11 furthercomprising allowing the user to select a metric in a generalized model.14. The system as defined in claim 11 wherein the reporting programallows a user to select at least one metric from the alternativerepresentation of the database to create structured query language (SQL)request based on the at least one metric, and wherein an interpreter ofthe database replaces labels of the SQL request to pertain to thepreviously created data.
 15. The system as defined in claim 11 whereinthe database comprises a reduced representation as the alternativerepresentation.
 16. A system comprising: a computer system having ameans for executing programs; a means for storing programs coupled tothe means for executing, the means for storing a reporting applicationexecutable by the means for executing; a database coupled to thecomputer system, the database storing a previously created data set;wherein the database comprises an alternative representation of thepreviously created data and an interpreter that executes search queriesgenerated from mappings stored in the alternative representation;wherein the reporting program allows a user to select at least onemetric from the alternative representation of the database to create arequest based on the at least one metric, and wherein an interpreter ofthe database modifies the request to pertain to the previously createddata.
 17. The system as defined in claim 16 further comprising allowingthe user to select a metric.